﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace devoirBD2
{
    class Bd
    {
        private static Bd instance;
        private string connectionString = "Data Source=(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=neptune.uqtr.ca)(PORT=1521)) (CONNECT_DATA=(SID=coursbd))); User Id=SMI1002_36; Password=43nxcq33;";
        private OracleConnection connection;
        private OracleCommand command;

        private Bd()
        {
            connection = new OracleConnection();
            command = new OracleCommand();
        }

        public static Bd getInstance()
        {
            if (instance == null)
            {
                instance = new Bd();
            }

            return instance;
        }

        public void openConnection()
        {
            connection.ConnectionString = connectionString;
            connection.Open();
            command.Connection = connection;
        }

        public void closeConnection()
        {
            connection.Close();
        }

        public OracleDataReader executeReader(string query)
        {
            command.CommandText = query;
            OracleDataReader dr = command.ExecuteReader();
            return dr;
        }

        public void executeUpdate(string query)
        {
            using (OracleCommand cmd = new OracleCommand(query, connection))
            {
                OracleTransaction trans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    OracleParameter prm = new OracleParameter();
                    cmd.BindByName = true;

                    cmd.ExecuteNonQuery();
                    trans.Commit();

                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
                finally
                {
                    trans.Dispose();
                }

            }
        }

        public DataSet executeDataSet(string query)
        {
            OracleDataAdapter da = new OracleDataAdapter(query, connection);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }

        public long executeInsert(string query, string id)
        {
            long return_id;
            query += " RETURNING " + id + " INTO :V_ID";       
            using (OracleCommand cmd = new OracleCommand(query, connection))
            {
                OracleTransaction trans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    OracleParameter prm = new OracleParameter();
                    cmd.BindByName = true;

                        
                    prm = new OracleParameter("V_ID", OracleDbType.Int32, ParameterDirection.ReturnValue); 
                    cmd.Parameters.Add(prm);

                    cmd.ExecuteNonQuery();
                    trans.Commit();


                    return_id = Convert.ToInt64(cmd.Parameters["V_ID"].Value.ToString());
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
                finally
                {
                    trans.Dispose();
                }
                    
            }

            return return_id;
            
        }

        public static long valideLong(object value)
        {
            if (value == DBNull.Value)
            {
                return 0;
            }

            return Convert.ToInt64((decimal)value);
        }

        public static DateTime valideDate(object value)
        {
            if (value == DBNull.Value)
            {
                return DateTime.MinValue;
            }

            return (DateTime)value;
        }

        public static string valideString(object value)
        {
            if (value == DBNull.Value)
            {
                return null;
            }

            return (string)value;
        }

        public static decimal valideDecimal(object value)
        {
            if (value == DBNull.Value)
            {
                return 0;
            }
            
            return Convert.ToDecimal(value);
        }
    }
}
